/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package util;

import java.sql.Connection;
import commonpackage.Eating;
import commonpackage.Hosting;
import commonpackage.Order;
import commonpackage.POI;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author chuda
 */
public class DBManagement {

    private Connection connection = null;
    private String drivername = "com.mysql.jdbc.Driver";
    private String source = "localhost:3306/poi";
    private String username = "mrc";
    private String password = "mrc";
    private String url = "jdbc:mysql://";

    /**
     * Constructor of DBManagement
     * @param source source of DB, e.g. "localhost:3306/rted"
     */
    public DBManagement() {

        try {
            Class.forName(drivername);
            this.url = url + source;
            this.connection = DriverManager.getConnection(url, username, password);

        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();

        } catch (SQLException ex) {
            ex.printStackTrace();

        }

    }

    /*Returns ArrayList of POIs by keyword*/
    public String[] selectPOIByKeyWord(String keyword) {
        String[] pois = new String[15];
        String query = SQLHelper.selectPOI(keyword);
        try {
            ResultSet rs = getConnection().createStatement().executeQuery(query);
            pois = Helper.convertResultSetIntoPOIS(rs);
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
        return pois;
    }

    public String[] selectPOIByProximity(double longitude, double latitude) {
        String[] pois = new String[15];
        return pois;
    }
    /*
     * to cancel an order
     **/
    public String cancelOrder (Order order) {
        String result = Helper.ORDER_CANCEL_SUCCESS;
        POI poi = order.getPoi();
        int numPeople = order.getNumPeople();
        int accomodation, numavrooms;
        String updatequery = "";
        if(poi instanceof Eating) {
            Eating eating = (Eating) poi;
            accomodation = eating.getAccomodation();
            updatequery = SQLHelper.updateAccomodation(poi.getId(), numPeople+accomodation);
        }else
            if(poi instanceof Hosting){
                Hosting hosting = (Hosting) poi;
                numavrooms = hosting.getNum_available_rooms();
                updatequery = SQLHelper.updateRooms(poi.getId(), numPeople + numavrooms);
            }
            String query = SQLHelper.deleteOrderByID(order.getOrderid());
        try {
            getConnection().setAutoCommit(false);
            if(!updatequery.equals(""))
                getConnection().createStatement().executeUpdate(updatequery);
            getConnection().createStatement().executeUpdate(query);
            getConnection().commit();
            getConnection().setAutoCommit(true);
        } catch (SQLException ex) {
            ex.printStackTrace();
            return Helper.UNKNOWN_ERROR;
        }
            return result;
    }
    /*
     * returns arraylist of eatings by keyword
     **/
    public String[] selectEatingByKeyWord(String keyword) {
        String[] pois = new String[15];
        String query = SQLHelper.selectEating(keyword);
        try {
            ResultSet rs = getConnection().createStatement().executeQuery(query);
            pois = Helper.convertResultSetIntoEating(rs);
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
        return pois;
    }

    public String[]selectEatingByProximity(double longitude, double latitude) {
        String[] pois = new String[15];
        return pois;
    }

    /*
     * Returns arrayList of Hostings by keyword
     **/
    public String[] selectHostingByKeyWord(String keyword) {
        String[] pois = new String[15];
        String query = SQLHelper.selectHosting(keyword);
        try {
            ResultSet rs = getConnection().createStatement().executeQuery(query);
            pois = Helper.convertResultSetIntoHosting(rs);
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
        return pois;
    }

    public String[] selectHostingByProximity(double longitude, double latitude) {
        String[] pois = new String[15];
        return pois;
    }
    /*
     * Returns arraylist of travels by keyword
     **/

    public String[] selectTravelByKeyWord(String keyword) {
        String[] pois = new String[15];
        String query = SQLHelper.selectTravel(keyword);
        try {
            ResultSet rs = getConnection().createStatement().executeQuery(query);
            pois = Helper.convertResultSetIntoTravel(rs);
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
        return pois;
    }

    public String[] selectTravelByProximity(double longitude, double latitude) {
        String[] pois = new String[15];
        return pois;
    }

    /*RETURNS ALL POIs from DB*/
    public String[] selectAllPOIs() {
        String[] pois = new String[15];
        String query = SQLHelper.selectAllPOIs();
        ResultSet rs;
        try {
            rs = getConnection().createStatement().executeQuery(query);
            pois = Helper.convertResultSetIntoPOIS(rs);
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
        return pois;
    }
    /*Inserts a new order and returns a string indicating success or error*/

    public String makeOrder(Order order) {
        String result = "Order Success!";
        String selectPOIquery = SQLHelper.selectPOIbyID(order.getPoiid());
        String updateQuery = "";
        try {
            ResultSet rs = getConnection().createStatement().executeQuery(selectPOIquery);
            if (rs == null) {
                return Helper.NO_SUCH_POI;
            }
            if (!order.getPoi().isTicket()) {
                return Helper.IMPOSSIBLE_ORDER;
            }
            if (order.getPoi() instanceof Eating) {
                rs.next();
                Eating eating = Helper.constructEatingFromRS(rs);
                int accom = eating.getAccomodation();
                if (order.getNumPeople() > accom) {
                    return Helper.NO_SPACE_RESTAURANT;
                } else {
                    updateQuery = SQLHelper.updateAccomodation(order.getPoiid(), accom - order.getNumPeople());
                }
            } else if (order.getPoi() instanceof Hosting) {
                rs.next();
                Hosting hosting = Helper.constructHostingFromRS(rs);
                int rooms = hosting.getNum_available_rooms();
                if (order.getNumPeople() > rooms) {
                    return Helper.NO_ROOMS_HOTEL;
                } else {
                    updateQuery = SQLHelper.updateRooms(order.getPoiid(), rooms - order.getNumPeople());
                }
            }

            String query = SQLHelper.insertOrder(order);
            getConnection().setAutoCommit(false);
            if (!updateQuery.equals("")) {
                getConnection().createStatement().executeUpdate(updateQuery);
            }
            getConnection().createStatement().executeUpdate(query);
            getConnection().commit();
            getConnection().setAutoCommit(true);
            //int result = getConnection()
        } catch (SQLException ex) {
            ex.printStackTrace();
            return Helper.UNKNOWN_ERROR;
        }

        return result;
    }

    /*returns arraylist of orders by telephone number*/
    public String[] selectOrdersByTelNumber(String telNumber) {
        String[] orders = new String[15];
        String query = SQLHelper.selectOrdersByMobNumber(telNumber);
        try {
            ResultSet rs = getConnection().createStatement().executeQuery(query);
            orders = Helper.convertResultSetIntoOrders(rs);
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
        return orders;
    }
    /*Return POI object by its id*/

    public POI selectPOIByID(int id) {
        POI poi = null;
        String query = SQLHelper.selectPOIbyID(id);
        try {
            ResultSet rs = getConnection().createStatement().executeQuery(query);
            rs.next();
            poi = Helper.constructPOIFromRS(rs);
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
        return poi;
    }

    /**
     * @return the connection
     */
    public Connection getConnection() {
        return connection;
    }

    /**
     * @param connection the connection to set
     */
    public void setConnection(Connection connection) {
        this.connection = connection;
    }
}
